package com.aba.cms.teacher;

import java.sql.*;
import java.time.DayOfWeek;
import java.time.LocalTime;
import java.util.ArrayList;
import java.util.List;

public class TeacherDAO {
    // 添加教师
    public void addTeacher(Teacher teacher) {
        String sql = "INSERT INTO teachers (name, department, title, phone, email) VALUES (?, ?, ?, ?, ?)";
        
        try (Connection conn = DBConnection.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
            
            pstmt.setString(1, teacher.getName());
            pstmt.setString(2, teacher.getDepartment());
            pstmt.setString(3, teacher.getTitle());
            pstmt.setString(4, teacher.getPhone());
            pstmt.setString(5, teacher.getEmail());
            
            int affectedRows = pstmt.executeUpdate();
            
            if (affectedRows > 0) {
                try (ResultSet generatedKeys = pstmt.getGeneratedKeys()) {
                    if (generatedKeys.next()) {
                        teacher.setId(generatedKeys.getInt(1));
                    }
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 更新教师信息
    public void updateTeacher(Teacher teacher) {
        String sql = "UPDATE teachers SET name = ?, department = ?, title = ?, phone = ?, email = ? WHERE id = ?";
        
        try (Connection conn = DBConnection.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setString(1, teacher.getName());
            pstmt.setString(2, teacher.getDepartment());
            pstmt.setString(3, teacher.getTitle());
            pstmt.setString(4, teacher.getPhone());
            pstmt.setString(5, teacher.getEmail());
            pstmt.setInt(6, teacher.getId());
            
            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 根据ID获取教师
    public Teacher getTeacherById(int id) {
        Teacher teacher = null;
        String sql = "SELECT * FROM teachers WHERE id = ?";
        
        try (Connection conn = DBConnection.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setInt(1, id);
            
            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    teacher = new Teacher();
                    teacher.setId(rs.getInt("id"));
                    teacher.setName(rs.getString("name"));
                    teacher.setDepartment(rs.getString("department"));
                    teacher.setTitle(rs.getString("title"));
                    teacher.setPhone(rs.getString("phone"));
                    teacher.setEmail(rs.getString("email"));
                    
                    // 获取教师所授课程
                    teacher.setCourses(getCoursesByTeacherId(id));
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        
        return teacher;
    }

    // 获取所有教师
    public List<Teacher> getAllTeachers() {
        List<Teacher> teachers = new ArrayList<>();
        String sql = "SELECT * FROM teachers";
        
        try (Connection conn = DBConnection.getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            
            while (rs.next()) {
                Teacher teacher = new Teacher();
                teacher.setId(rs.getInt("id"));
                teacher.setName(rs.getString("name"));
                teacher.setDepartment(rs.getString("department"));
                teacher.setTitle(rs.getString("title"));
                teacher.setPhone(rs.getString("phone"));
                teacher.setEmail(rs.getString("email"));
                
                // 获取教师所授课程
                teacher.setCourses(getCoursesByTeacherId(teacher.getId()));
                
                teachers.add(teacher);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        
        return teachers;
    }

    // 根据姓名查询教师
    public List<Teacher> searchTeachersByName(String name) {
        List<Teacher> teachers = new ArrayList<>();
        String sql = "SELECT * FROM teachers WHERE name LIKE ?";
        
        try (Connection conn = DBConnection.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setString(1, "%" + name + "%");
            
            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    Teacher teacher = new Teacher();
                    teacher.setId(rs.getInt("id"));
                    teacher.setName(rs.getString("name"));
                    teacher.setDepartment(rs.getString("department"));
                    teacher.setTitle(rs.getString("title"));
                    teacher.setPhone(rs.getString("phone"));
                    teacher.setEmail(rs.getString("email"));
                    
                    // 获取教师所授课程
                    teacher.setCourses(getCoursesByTeacherId(teacher.getId()));
                    
                    teachers.add(teacher);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        
        return teachers;
    }

    // 获取教师所授课程
    private List<Course> getCoursesByTeacherId(int teacherId) {
        List<Course> courses = new ArrayList<>();
        String sql = "SELECT * FROM courses WHERE teacher_id = ?";
        
        try (Connection conn = DBConnection.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setInt(1, teacherId);
            
            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    Course course = new Course();
                    course.setId(rs.getInt("id"));
                    course.setCourseName(rs.getString("course_name"));
                    course.setCourseCode(rs.getString("course_code"));
                    course.setClassroom(rs.getString("classroom"));
                    
                    // 转换星期
                    int dayOfWeekInt = rs.getInt("day_of_week");
                    DayOfWeek dayOfWeek = DayOfWeek.of(dayOfWeekInt);
                    course.setDayOfWeek(dayOfWeek);
                    
                    // 转换时间
                    LocalTime startTime = rs.getTime("start_time").toLocalTime();
                    LocalTime endTime = rs.getTime("end_time").toLocalTime();
                    course.setStartTime(startTime);
                    course.setEndTime(endTime);
                    
                    // 设置授课教师
                    Teacher teacher = new Teacher();
                    teacher.setId(teacherId);
                    // 这里只设置了教师ID，如果需要完整信息，可以进一步查询
                    course.setTeacher(teacher);
                    
                    courses.add(course);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        
        return courses;
    }
}    